package com.sthz.facepay.mapper;

import com.sthz.facepay.model.Agent;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface AgentMapper {
    @Delete({
        "delete from st_agent",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer id);

    @Insert({
        "insert into st_agent (id, account, ",
        "password, name, ",
        "account_type, grade, ",
        "cooperation_type, state, ",
        "legal_person, phone, ",
        "indirect_rate, direct_rate, ",
        "start_time, end_time, ",
        "id_positive, id_back, ",
        "business_license, contacts, ",
        "agent_area, agent_address, ",
        "detailed_address, agent_phone, ",
        "agent_qq, alipay_number, ",
        "cash_bank, cardholder, ",
        "id_number, bank_number, ",
        "remark, picture)",
        "values (#{id,jdbcType=INTEGER}, #{account,jdbcType=VARCHAR}, ",
        "#{password,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, ",
        "#{account_type,jdbcType=TINYINT}, #{grade,jdbcType=TINYINT}, ",
        "#{cooperation_type,jdbcType=TINYINT}, #{state,jdbcType=TINYINT}, ",
        "#{legal_person,jdbcType=VARCHAR}, #{phone,jdbcType=VARCHAR}, ",
        "#{indirect_rate,jdbcType=INTEGER}, #{direct_rate,jdbcType=INTEGER}, ",
        "#{start_time,jdbcType=TIMESTAMP}, #{end_time,jdbcType=TIMESTAMP}, ",
        "#{id_positive,jdbcType=VARCHAR}, #{id_back,jdbcType=VARCHAR}, ",
        "#{business_license,jdbcType=VARCHAR}, #{contacts,jdbcType=VARCHAR}, ",
        "#{agent_area,jdbcType=VARCHAR}, #{agent_address,jdbcType=VARCHAR}, ",
        "#{detailed_address,jdbcType=VARCHAR}, #{agent_phone,jdbcType=VARCHAR}, ",
        "#{agent_qq,jdbcType=VARCHAR}, #{alipay_number,jdbcType=VARCHAR}, ",
        "#{cash_bank,jdbcType=VARCHAR}, #{cardholder,jdbcType=VARCHAR}, ",
        "#{id_number,jdbcType=VARCHAR}, #{bank_number,jdbcType=VARCHAR}, ",
        "#{remark,jdbcType=VARCHAR}, #{picture,jdbcType=VARCHAR})"
    })
    /**
     * 新增代理商
     */
    boolean insert(Agent agent);

    @InsertProvider(type=AgentSqlProvider.class, method="insertSelective")
    int insertSelective(Agent record);

    @Select({
        "select",
        "id, account, password, name, account_type, grade, cooperation_type, state, legal_person, ",
        "phone, indirect_rate, direct_rate, start_time, end_time, id_positive, id_back, ",
        "business_license, contacts, agent_area, agent_address, detailed_address, agent_phone, ",
        "agent_qq, alipay_number, cash_bank, cardholder, id_number, bank_number, remark, ",
        "picture",
        "from st_agent",
        "where id = #{id,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
        @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
        @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
        @Result(column="account_type", property="account_type", jdbcType=JdbcType.TINYINT),
        @Result(column="grade", property="grade", jdbcType=JdbcType.TINYINT),
        @Result(column="cooperation_type", property="cooperation_type", jdbcType=JdbcType.TINYINT),
        @Result(column="state", property="state", jdbcType=JdbcType.TINYINT),
        @Result(column="legal_person", property="legal_person", jdbcType=JdbcType.VARCHAR),
        @Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
        @Result(column="indirect_rate", property="indirect_rate", jdbcType=JdbcType.INTEGER),
        @Result(column="direct_rate", property="direct_rate", jdbcType=JdbcType.INTEGER),
        @Result(column="start_time", property="start_time", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="end_time", property="end_time", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="id_positive", property="id_positive", jdbcType=JdbcType.VARCHAR),
        @Result(column="id_back", property="id_back", jdbcType=JdbcType.VARCHAR),
        @Result(column="business_license", property="business_license", jdbcType=JdbcType.VARCHAR),
        @Result(column="contacts", property="contacts", jdbcType=JdbcType.VARCHAR),
        @Result(column="agent_area", property="agent_area", jdbcType=JdbcType.VARCHAR),
        @Result(column="agent_address", property="agent_address", jdbcType=JdbcType.VARCHAR),
        @Result(column="detailed_address", property="detailed_address", jdbcType=JdbcType.VARCHAR),
        @Result(column="agent_phone", property="agent_phone", jdbcType=JdbcType.VARCHAR),
        @Result(column="agent_qq", property="agent_qq", jdbcType=JdbcType.VARCHAR),
        @Result(column="alipay_number", property="alipay_number", jdbcType=JdbcType.VARCHAR),
        @Result(column="cash_bank", property="cash_bank", jdbcType=JdbcType.VARCHAR),
        @Result(column="cardholder", property="cardholder", jdbcType=JdbcType.VARCHAR),
        @Result(column="id_number", property="id_number", jdbcType=JdbcType.VARCHAR),
        @Result(column="bank_number", property="bank_number", jdbcType=JdbcType.VARCHAR),
        @Result(column="remark", property="remark", jdbcType=JdbcType.VARCHAR),
        @Result(column="picture", property="picture", jdbcType=JdbcType.VARCHAR)
    })
    Agent selectByPrimaryKey(Integer id);

    @UpdateProvider(type=AgentSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(Agent record);

    @Update({
        "update st_agent",
        "set account = #{account,jdbcType=VARCHAR},",
          "password = #{password,jdbcType=VARCHAR},",
          "name = #{name,jdbcType=VARCHAR},",
          "account_type = #{account_type,jdbcType=TINYINT},",
          "grade = #{grade,jdbcType=TINYINT},",
          "cooperation_type = #{cooperation_type,jdbcType=TINYINT},",
          "state = #{state,jdbcType=TINYINT},",
          "legal_person = #{legal_person,jdbcType=VARCHAR},",
          "phone = #{phone,jdbcType=VARCHAR},",
          "indirect_rate = #{indirect_rate,jdbcType=INTEGER},",
          "direct_rate = #{direct_rate,jdbcType=INTEGER},",
          "start_time = #{start_time,jdbcType=TIMESTAMP},",
          "end_time = #{end_time,jdbcType=TIMESTAMP},",
          "id_positive = #{id_positive,jdbcType=VARCHAR},",
          "id_back = #{id_back,jdbcType=VARCHAR},",
          "business_license = #{business_license,jdbcType=VARCHAR},",
          "contacts = #{contacts,jdbcType=VARCHAR},",
          "agent_area = #{agent_area,jdbcType=VARCHAR},",
          "agent_address = #{agent_address,jdbcType=VARCHAR},",
          "detailed_address = #{detailed_address,jdbcType=VARCHAR},",
          "agent_phone = #{agent_phone,jdbcType=VARCHAR},",
          "agent_qq = #{agent_qq,jdbcType=VARCHAR},",
          "alipay_number = #{alipay_number,jdbcType=VARCHAR},",
          "cash_bank = #{cash_bank,jdbcType=VARCHAR},",
          "cardholder = #{cardholder,jdbcType=VARCHAR},",
          "id_number = #{id_number,jdbcType=VARCHAR},",
          "bank_number = #{bank_number,jdbcType=VARCHAR},",
          "remark = #{remark,jdbcType=VARCHAR},",
          "picture = #{picture,jdbcType=VARCHAR}",
        "where id = #{id,jdbcType=INTEGER}"
    })
    /**
     * 根据ID修改代理商信息
     */
    boolean updateByPrimaryKey(Agent agent);



    @Select({
            "select",
            "id, account, password, name, account_type, grade, cooperation_type, state, legal_person, ",
            "phone, indirect_rate, direct_rate, start_time, end_time, id_positive, id_back, ",
            "business_license, contacts, agent_area, agent_address, detailed_address, agent_phone, ",
            "agent_qq, alipay_number, cash_bank, cardholder, id_number, bank_number, remark, ",
            "picture",
            "from st_agent",
            "where id = #{id,jdbcType=INTEGER}"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
            @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="account_type", property="account_type", jdbcType=JdbcType.TINYINT),
            @Result(column="grade", property="grade", jdbcType=JdbcType.TINYINT),
            @Result(column="cooperation_type", property="cooperation_type", jdbcType=JdbcType.TINYINT),
            @Result(column="state", property="state", jdbcType=JdbcType.TINYINT),
            @Result(column="legal_person", property="legal_person", jdbcType=JdbcType.VARCHAR),
            @Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="indirect_rate", property="indirect_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="direct_rate", property="direct_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="start_time", property="start_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="end_time", property="end_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="id_positive", property="id_positive", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_back", property="id_back", jdbcType=JdbcType.VARCHAR),
            @Result(column="business_license", property="business_license", jdbcType=JdbcType.VARCHAR),
            @Result(column="contacts", property="contacts", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_area", property="agent_area", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_address", property="agent_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="detailed_address", property="detailed_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_phone", property="agent_phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_qq", property="agent_qq", jdbcType=JdbcType.VARCHAR),
            @Result(column="alipay_number", property="alipay_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="cash_bank", property="cash_bank", jdbcType=JdbcType.VARCHAR),
            @Result(column="cardholder", property="cardholder", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_number", property="id_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="bank_number", property="bank_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="remark", property="remark", jdbcType=JdbcType.VARCHAR),
            @Result(column="picture", property="picture", jdbcType=JdbcType.VARCHAR)
    })
    Agent getById(Integer id);

    @Select({
            "select",
            "id, account, password, name, account_type, grade, cooperation_type, state, legal_person, ",
            "phone, indirect_rate, direct_rate, start_time, end_time, id_positive, id_back, ",
            "business_license, contacts, agent_area, agent_address, detailed_address, agent_phone, ",
            "agent_qq, alipay_number, cash_bank, cardholder, id_number, bank_number, remark, ",
            "picture",
            "from st_agent"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
            @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="account_type", property="account_type", jdbcType=JdbcType.TINYINT),
            @Result(column="grade", property="grade", jdbcType=JdbcType.TINYINT),
            @Result(column="cooperation_type", property="cooperation_type", jdbcType=JdbcType.TINYINT),
            @Result(column="state", property="state", jdbcType=JdbcType.TINYINT),
            @Result(column="legal_person", property="legal_person", jdbcType=JdbcType.VARCHAR),
            @Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="indirect_rate", property="indirect_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="direct_rate", property="direct_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="start_time", property="start_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="end_time", property="end_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="id_positive", property="id_positive", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_back", property="id_back", jdbcType=JdbcType.VARCHAR),
            @Result(column="business_license", property="business_license", jdbcType=JdbcType.VARCHAR),
            @Result(column="contacts", property="contacts", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_area", property="agent_area", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_address", property="agent_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="detailed_address", property="detailed_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_phone", property="agent_phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_qq", property="agent_qq", jdbcType=JdbcType.VARCHAR),
            @Result(column="alipay_number", property="alipay_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="cash_bank", property="cash_bank", jdbcType=JdbcType.VARCHAR),
            @Result(column="cardholder", property="cardholder", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_number", property="id_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="bank_number", property="bank_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="remark", property="remark", jdbcType=JdbcType.VARCHAR),
            @Result(column="picture", property="picture", jdbcType=JdbcType.VARCHAR)
    })
    /**
     * 查询代理商列表
     */
    List<Agent> findAll();

    @Select({
            "<script>",
            "select",
            "id, account, password, name, account_type, grade, cooperation_type, state, legal_person, ",
            "phone, indirect_rate, direct_rate, start_time, end_time, id_positive, id_back, ",
            "business_license, contacts, agent_area, agent_address, detailed_address, agent_phone, ",
            "agent_qq, alipay_number, cash_bank, cardholder, id_number, bank_number, remark, ",
            "picture",
            "from st_agent where 1=1 ",
            "<if test=\"name != null and name != ''\">",
            "and name like CONCAT('%',#{name,jdbcType=VARCHAR},'%')",
            "</if>",
            "</script>"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
            @Result(column="password", property="password", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="account_type", property="account_type", jdbcType=JdbcType.TINYINT),
            @Result(column="grade", property="grade", jdbcType=JdbcType.TINYINT),
            @Result(column="cooperation_type", property="cooperation_type", jdbcType=JdbcType.TINYINT),
            @Result(column="state", property="state", jdbcType=JdbcType.TINYINT),
            @Result(column="legal_person", property="legal_person", jdbcType=JdbcType.VARCHAR),
            @Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="indirect_rate", property="indirect_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="direct_rate", property="direct_rate", jdbcType=JdbcType.INTEGER),
            @Result(column="start_time", property="start_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="end_time", property="end_time", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="id_positive", property="id_positive", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_back", property="id_back", jdbcType=JdbcType.VARCHAR),
            @Result(column="business_license", property="business_license", jdbcType=JdbcType.VARCHAR),
            @Result(column="contacts", property="contacts", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_area", property="agent_area", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_address", property="agent_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="detailed_address", property="detailed_address", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_phone", property="agent_phone", jdbcType=JdbcType.VARCHAR),
            @Result(column="agent_qq", property="agent_qq", jdbcType=JdbcType.VARCHAR),
            @Result(column="alipay_number", property="alipay_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="cash_bank", property="cash_bank", jdbcType=JdbcType.VARCHAR),
            @Result(column="cardholder", property="cardholder", jdbcType=JdbcType.VARCHAR),
            @Result(column="id_number", property="id_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="bank_number", property="bank_number", jdbcType=JdbcType.VARCHAR),
            @Result(column="remark", property="remark", jdbcType=JdbcType.VARCHAR),
            @Result(column="picture", property="picture", jdbcType=JdbcType.VARCHAR)
    })
    /**
     * 根据代理商名称模糊查询
     */
    List<Agent> selectAgentByName(@Param("name") String name);


    @Select({
            "select count(*) from st_agent"
    })
    /**
     * 总代理商数
     */
    int getAgentTotal();

    @Select({
            "select count(*) from st_agent where yearweek(date_format(start_time,'%Y-%m-%d')) = yearweek(now())"
    })
    /**
     * 本周新增代理商数
     */
    int getWeekNewAddAgentTotal();

    @Select({
            "select count(*) from st_agent where state = 0"
    })
    /**
     * 正在使用代理商数
     */
    int getInUseAgentTotal();

    @Select({
            "select count(*) from st_agent where end_time < now()"
    })
    /**
     * 过期代理商数
     */
    int getExpireAgentTotal();
}